using System;
using System.Data;
using System.Data.SqlClient;
using DTN.Core;
using DTN.Core.Utilities;
using DTN.Core.Common;
namespace com.dtn.deals
{
	public class tbl_AreaDB
	{
		public static DataTable GetAll ()  
		{
 			DataTable retVal = null;
			SqlConnection dbConn = new SqlConnection(DataHelper.GetConnectionString());
 			SqlCommand dbCmd = new SqlCommand("tbl_Area_GetAll", dbConn);
			dbCmd.CommandType = CommandType.StoredProcedure;
			try
			{
				retVal = new DataTable() ;
				SqlDataAdapter da = new SqlDataAdapter(dbCmd);
				da.Fill(retVal);
			}
			finally
			{
				dbConn.Close();
			}
			return retVal; 
		}

        public static DataTable SelectAllAreaByProviceId(int id)
        {
            DataTable retVal = null;
            SqlConnection dbConn = new SqlConnection(DataHelper.GetConnectionString());
            SqlCommand dbCmd = new SqlCommand("sp_SelectAreaByProviceId", dbConn);
            dbCmd.Parameters.AddWithValue("@ProvinceId", id);
            dbCmd.CommandType = CommandType.StoredProcedure;
            try
            {
                retVal = new DataTable();
                SqlDataAdapter da = new SqlDataAdapter(dbCmd);
                da.Fill(retVal);
            }
            finally
            {
                dbConn.Close();
            }
            return retVal; 
        }
		public static void Delete (int iD)
		{
			SqlConnection dbConn = new SqlConnection(DataHelper.GetConnectionString());
			SqlCommand dbCmd = new SqlCommand("tbl_Area_Delete", dbConn);
			dbCmd.CommandType = CommandType.StoredProcedure;
			dbCmd.Parameters.AddWithValue("@ID",iD);
			try
			{
				dbConn.Open();
				dbCmd.ExecuteNonQuery();
			}
			finally
			{
				dbConn.Close();
			}
		}
		public static int Insert (tbl_AreaInfo tbl_AreaInfo)
		{
            SqlConnection dbConn = new SqlConnection(DataHelper.GetConnectionString());
			SqlCommand dbCmd = new SqlCommand("tbl_Area_Insert", dbConn);
			dbCmd.CommandType = CommandType.StoredProcedure;
 					dbCmd.Parameters.AddWithValue("@cityId",tbl_AreaInfo.cityId);
					dbCmd.Parameters.AddWithValue("@Name",tbl_AreaInfo.Name);
					dbCmd.Parameters.AddWithValue("@cDate",tbl_AreaInfo.cDate);
			dbCmd.Parameters.Add("@RETURN_VALUE", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;
			try {
				dbConn.Open();
				dbCmd.ExecuteNonQuery();
				return (int)dbCmd.Parameters["@RETURN_VALUE"].Value;
			}
			finally 
			{
				dbConn.Close();
			}
		}
 
		public static void Update (tbl_AreaInfo tbl_AreaInfo)
		{
            SqlConnection dbConn = new SqlConnection(DataHelper.GetConnectionString());
			SqlCommand dbCmd = new SqlCommand("tbl_Area_Update", dbConn);
			dbCmd.CommandType = CommandType.StoredProcedure;
					dbCmd.Parameters.AddWithValue("@ID",tbl_AreaInfo.ID);
                    dbCmd.Parameters.AddWithValue("@cityId", tbl_AreaInfo.cityId);
                    dbCmd.Parameters.AddWithValue("@Name", tbl_AreaInfo.Name);
                    dbCmd.Parameters.AddWithValue("@cDate", tbl_AreaInfo.cDate);
			try {
				dbConn.Open();
				dbCmd.ExecuteNonQuery();
			}
			finally
			{
				dbConn.Close();
			}
		}
 
		public static tbl_AreaInfo GetInfo ( int iD )
		{
			tbl_AreaInfo retVal = null;
            SqlConnection dbConn = new SqlConnection(DataHelper.GetConnectionString());
			SqlCommand dbCmd = new SqlCommand("tbl_Area_GetInfo", dbConn);
			dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@ID", iD);
			try 
			{
				dbConn.Open();
				SqlDataReader dr = dbCmd.ExecuteReader();
				if (dr.Read()) retVal = new tbl_AreaInfo(dr);
			}
			finally
			{
				dbConn.Close();
			}
			return retVal;
		}

        public static DataTable GetAllByTopAndCityId(int parentcity,int cityid, int top)
        {
            DataTable retVal = null;
            SqlConnection dbConn = new SqlConnection(DataHelper.GetConnectionString());
            SqlCommand dbCmd = new SqlCommand("tbl_Area_GetAllByTop", dbConn);
            dbCmd.Parameters.AddWithValue("@top", top);
            dbCmd.Parameters.AddWithValue("@ParentCity", parentcity);
            dbCmd.Parameters.AddWithValue("@cityid", cityid);
            dbCmd.CommandType = CommandType.StoredProcedure;
            try
            {
                retVal = new DataTable();
                SqlDataAdapter da = new SqlDataAdapter(dbCmd);
                da.Fill(retVal);
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }
        public static int GetCountByCityId(int cityId)
        {
            int count = 0;
            DataTable retVal = new DataTable();
            SqlConnection dbConn = new SqlConnection(AppEnv.ConnectionString);

            SqlCommand dbCmd = new SqlCommand("tbl_Area_GetCountByCityId", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@CityID", cityId);
            try
            {
                dbConn.Open();
                SqlDataAdapter da = new SqlDataAdapter(dbCmd);
                object returnRole = dbCmd.ExecuteScalar();
                if (returnRole != null && returnRole != DBNull.Value)
                    count = ConvertUtility.ToInt32(returnRole);
            }
            finally
            {
                dbConn.Close();
            }
            return count;
        }
	}
}

